﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Oracle.ManagedDataAccess.Client;
using VswService_V7.Utility;

namespace VswService_V7.DataBase.Tables
{
    /// <summary>
    /// wp code
    /// </summary>
    public partial class BATCH_SAMPLE_RULE
    {
        #region 1116wp
        /// <summary>
        /// select batchsamplerule table data on the first page
        /// </summary>
        /// <param name="minRow"></param>
        /// <param name="maxRow"></param>
        /// <returns></returns>
        public static DataTable Select_BatchSampleRule(int minRow, int maxRow)
        {
            try
            {
                string sqlrecipe = string.Format("select * from (select a.*,ROWNUM rn from {0} a order by {1} desc) where rn>{2} and rn<={3}",
                    tableName, fd_updateTime, minRow, maxRow);

                DataTable recipetable = OracleHelper.Exct_QueryTable(sqlrecipe);
                if (recipetable != null)
                {
                    return recipetable;
                }
                else
                    return null;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("Select_BatchSampleRule", ex);
                throw (ex);
            }
        }
        /// <summary>
        /// insert batchsamplerule table data
        /// </summary>
        /// <param name="batchTable"></param>
        /// <returns></returns>
        public static DataTable Insert_BatchSampleRule(DataTable batchTable)
        {
            try
            {
                if (ExcuteNewBatchSampleRule(batchTable) > 0)
                {
                    return batchTable;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("Insert_BatchSampleRule", ex);
                throw ex;
            }
        }
        private static int ExcuteNewBatchSampleRule(DataTable dataTable)
        {
            try
            {
                string sql = string.Format("insert into {0} values('{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}','{25}')", tableName,
                  dataTable.Rows[0][fd_sampleName].ToString(), dataTable.Rows[0][fd_sampleNo].ToString(), dataTable.Rows[0][fd_cycleQuanti1].ToString(),
                  dataTable.Rows[0][fd_fltLength1].ToString(), dataTable.Rows[0][fd_mtLength1].ToString(), dataTable.Rows[0][fd_atLength1].ToString(),
                  dataTable.Rows[0][fd_fltLength2].ToString(), dataTable.Rows[0][fd_mtLength2].ToString(), dataTable.Rows[0][fd_atLength2].ToString(),
                  dataTable.Rows[0][fd_fltLength3].ToString(), dataTable.Rows[0][fd_mtLength3].ToString(), dataTable.Rows[0][fd_atLength3].ToString(),
                  dataTable.Rows[0][fd_fltLength4].ToString(), dataTable.Rows[0][fd_mtLength4].ToString(), dataTable.Rows[0][fd_atLength4].ToString(),
                  dataTable.Rows[0][fd_fltLength5].ToString(), dataTable.Rows[0][fd_mtLength5].ToString(), dataTable.Rows[0][fd_atLength5].ToString(),
                  dataTable.Rows[0][fd_fltLength6].ToString(), dataTable.Rows[0][fd_mtLength6].ToString(), dataTable.Rows[0][fd_atLength6].ToString(),
                  dataTable.Rows[0][fd_fltLength7].ToString(), dataTable.Rows[0][fd_mtLength7].ToString(), dataTable.Rows[0][fd_atLength7].ToString(), dataTable.Rows[0][fd_updateTime].ToString());
                if (OracleHelper.Exct_NonQuery(sql) > 0)
                {
                    LogHelper.WriteLog("ExcuteNewBatchSampleRule Success");
                    return 1;
                }
                else
                {
                    return -1;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("ExcuteNewBatchSampleRule", ex);
                throw ex;
            }
        }
        /// <summary>
        /// edit batch samplerule table data
        /// </summary>
        /// <param name="batchTable"></param>
        /// <returns></returns>
        public static DataTable Edit_BatchSampleRule(DataTable batchTable)
        {
            try
            {
                if (ExcuteEditBatchSampleRule(batchTable) > 0)
                {
                    return batchTable;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("Edit_BatchSampleRule", ex);
                throw ex;
            }
        }
        private static int ExcuteEditBatchSampleRule(DataTable dataTable)
        {
            try
            {
                string sql = string.Format("UPDATE {0} SET {1}='{2}', {3}='{4}', {5}='{6}', {7}='{8}',{9}='{10}', {11}='{12}',{13}='{14}', {15}='{16}', {17}='{18}', {19}='{20}',{21}='{22}', " +
                                    "{23}='{24}', {25}='{26}', {27}='{28}', {29}='{30}',{31}='{32}', {33}='{34}',{35}='{36}', {37}='{38}', {39}='{40}', {41}='{42}',{43}='{44}'," +
                                    "{45}='{46}', {47}='{48}' WHERE {49}='{50}'",
                                    tableName, fd_sampleName, dataTable.Rows[0][fd_sampleName].ToString(), fd_cycleQuanti1, dataTable.Rows[0][fd_cycleQuanti1].ToString(),
                                    fd_fltLength1, dataTable.Rows[0][fd_fltLength1].ToString(), fd_mtLength1, dataTable.Rows[0][fd_mtLength1].ToString(), fd_atLength1, dataTable.Rows[0][fd_atLength1].ToString(),
                                    fd_fltLength2, dataTable.Rows[0][fd_fltLength2].ToString(), fd_mtLength2, dataTable.Rows[0][fd_mtLength2].ToString(), fd_atLength2, dataTable.Rows[0][fd_atLength2].ToString(),
                                    fd_fltLength3, dataTable.Rows[0][fd_fltLength3].ToString(), fd_mtLength3, dataTable.Rows[0][fd_mtLength3].ToString(), fd_atLength3, dataTable.Rows[0][fd_atLength3].ToString(),
                                    fd_fltLength4, dataTable.Rows[0][fd_fltLength4].ToString(), fd_mtLength4, dataTable.Rows[0][fd_mtLength4].ToString(), fd_atLength4, dataTable.Rows[0][fd_atLength4].ToString(),
                                    fd_fltLength5, dataTable.Rows[0][fd_fltLength5].ToString(), fd_mtLength5, dataTable.Rows[0][fd_mtLength5].ToString(), fd_atLength5, dataTable.Rows[0][fd_atLength5].ToString(),
                                    fd_fltLength6, dataTable.Rows[0][fd_fltLength6].ToString(), fd_mtLength6, dataTable.Rows[0][fd_mtLength6].ToString(), fd_atLength6, dataTable.Rows[0][fd_atLength6].ToString(),
                                    fd_fltLength7, dataTable.Rows[0][fd_fltLength7].ToString(), fd_mtLength7, dataTable.Rows[0][fd_mtLength7].ToString(), fd_atLength7, dataTable.Rows[0][fd_atLength7].ToString(),
                                    fd_updateTime, dataTable.Rows[0][fd_updateTime].ToString(), fd_sampleNo, dataTable.Rows[0][fd_sampleNo].ToString()
                                    );
                if (OracleHelper.Exct_NonQuery(sql) > 0)
                {
                    LogHelper.WriteLog("ExcuteEditBatchSampleRule Success");
                    return 1;
                }
                else
                {
                    return -1;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("ExcuteEditBatchSampleRule", ex);
                throw ex;
            }
        }
        /// <summary>
        /// delete batchsamplerule table data
        /// </summary>
        /// <param name="sampleNo"></param>
        /// <returns></returns>
        public static DataTable Delete_BatchSampleRule(string sampleNo)
        {
            try
            {
                string deleteBatchSql = string.Format("delete from {0} where {1}='{2}'", tableName, fd_sampleNo, sampleNo);
                if (OracleHelper.Exct_NonQuery(deleteBatchSql) > 0)
                {
                    return Select_BatchSampleRule(0, 20);
                }
                else
                {
                    return null;
                }
            }
            catch
            {
                return null;
            }
        }

        #endregion

        #region 1126wp
        /// <summary>
        /// select batchsamplerule table data of a certain page
        /// </summary>
        /// <param name="minRow"></param>
        /// <param name="maxRow"></param>
        /// <returns></returns>
        public static DataTable Select_BatchSampleRuleByIndex(int minRow, int maxRow)
        {
            try
            {
                string sqlBatch = string.Format("select * from (select a.*,ROWNUM rn from {0} a order by {1} desc) where rn>{2} and rn<={3}",
                  tableName, fd_updateTime, minRow, maxRow);

                DataTable batchTable = OracleHelper.Exct_QueryTable(sqlBatch);
                if (batchTable != null)
                {
                    return batchTable;
                }
                else
                    return null;
            }
            catch (Exception ex)
            {
                return null;
                throw (ex);
            }
        }
        #endregion

        #region 1204wp
        /// <summary>
        /// select batchsamplerule table data of a certain page
        /// </summary>
        /// <param name="minRow"></param>
        /// <param name="maxRow"></param>
        /// <param name="batcSampleRuleTotalPages"></param>
        /// <returns></returns>
        public static DataTable Select_BatchSampleRuleByIndex(int minRow, int maxRow, out string batcSampleRuleTotalPages)
        {
            try
            {
                string sqlBatch = string.Format("select * from (select ROWNUM rn,a.* from (select * from {0} order by {1} desc) a) where rn>{2} and rn<={3}",
                 tableName, fd_updateTime, minRow, maxRow);
                string sqlbatchcount = string.Format("select count(*) from {0}",
                  tableName);
                DataTable batchtable = OracleHelper.Exct_QueryTable(sqlBatch);
                if (batchtable != null)
                {
                    int batchPages = Convert.ToInt32(TASK_INFO.GetTableCount(sqlbatchcount));
                    if (batchPages % pageNum == 0)
                    {
                        batcSampleRuleTotalPages = (batchPages / pageNum).ToString();
                    }
                    else
                    {
                        batcSampleRuleTotalPages = (batchPages / pageNum + 1).ToString();
                    }
                    LogHelper.WriteLog("Select_BatchSampleRuleByIndex Success");
                    return batchtable;
                }
                else
                {
                    batcSampleRuleTotalPages = "";
                    return null; ;
                }

            }
            catch (Exception ex)
            {
                batcSampleRuleTotalPages = "";
                LogHelper.WriteLog("Select_BatchSampleRuleByIndex", ex);
                throw ex;
            }
        }
        #endregion
    }
}
